-- =============================================
-- This stored procedure resets Nav_Menu records and content records
-- with proper Pre and PostFuseURL.

-- This should be run on setup, deployment or whenever GECodePath changes.
-- Normally should be used for admin website or a site with only dynamic contents.
-- This is because for fused site, published files need to be moved and link should be updated.
-- =============================================
CREATE  PROCEDURE amsp_CMResetNavContentURL
  @InWebsiteKey uniqueidentifier  
AS
BEGIN
  DECLARE
    @NavMenuID numeric,
    @ContentID numeric,
    @PreFuseURL varchar(500),
    @PostFuseURL varchar(500),
    @NewPreFuseURL varchar(500),
    @NewPostFuseURL varchar(500)

  DECLARE c_NavMenu CURSOR FOR
   SELECT NavMenuID,
          PreFuseURL,
          PostFuseURL
     FROM Nav_Menu
    WHERE WebsiteKey = @InWebsiteKey

  OPEN c_NavMenu
  FETCH NEXT FROM c_NavMenu
   INTO @NavMenuID,
        @PreFuseURL,
        @PostFuseURL

  WHILE @@FETCH_STATUS = 0 BEGIN
    EXECUTE amsp_CMGetFuseURL @NavMenuID, NULL, @NewPreFuseURL OUTPUT, @NewPostFuseURL OUTPUT
    
    IF IsNull(@NewPreFuseURL,'') <> IsNull(@PreFuseURL,'') OR IsNull(@NewPostFuseURL,'') <> IsNull(@PostFuseURL,'')
      UPDATE Nav_Menu
         SET PreFuseURL = @NewPreFuseURL,
             PostFuseURL = @NewPostFuseURL
       WHERE NavMenuID = @NavMenuID

    FETCH NEXT FROM c_NavMenu
     INTO @NavMenuID,
          @PreFuseURL,
          @PostFuseURL
  END
  CLOSE c_NavMenu
  DEALLOCATE c_NavMenu


  DECLARE c_Content CURSOR FOR
   SELECT a.NavMenuID,
          a.ContentID,
          a.PreFuseURL,
          a.PostFuseURL
     FROM Content a, Nav_Menu b
    WHERE a.NavMenuID = b.NavMenuID
      AND b.WebsiteKey = @InWebsiteKey

  OPEN c_Content
  FETCH NEXT FROM c_Content
   INTO @NavMenuID,
        @ContentID,
        @PreFuseURL,
        @PostFuseURL

  WHILE @@FETCH_STATUS = 0 BEGIN
    EXECUTE amsp_CMGetFuseURL @NavMenuID, @ContentID, @NewPreFuseURL OUTPUT, @NewPostFuseURL OUTPUT

    IF IsNull(@NewPreFuseURL,'') <> IsNull(@PreFuseURL,'') OR IsNull(@NewPostFuseURL,'') <> IsNull(@PostFuseURL,'')
      UPDATE Content
         SET PreFuseURL = @NewPreFuseURL,
             PostFuseURL = @NewPostFuseURL
       WHERE ContentID = @ContentID

    FETCH NEXT FROM c_Content
     INTO @NavMenuID,
          @ContentID,
          @PreFuseURL,
          @PostFuseURL
  END
  CLOSE c_Content
  DEALLOCATE c_Content

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMResetNavContentURL] TO [IMIS]
GO